package com.dragon.codefx.utils;

import com.dragon.codefx.dataBase.entity.DatabaseConfigEntity;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import static com.dragon.codefx.utils.JdbcUtil.identifyDatabaseType;
import static com.dragon.codefx.utils.SchemaUtils.buildJdbcUrlWithDefaultParams;


/**
 * 数据库表工具类
 * @author lsc
 * @since 2025/6/20 16:09
 **/
public class TableUtils {
    @SneakyThrows
    public static List<Map<String, Object>> tableList(String schemaName, DatabaseConfigEntity entity) {

        // 获取数据库连接信息
        String jdbcUrl = buildJdbcUrlWithDefaultParams(entity.getUrl(), entity.getPort(), entity.getType(), schemaName);
        // 获取数据库连接信息
        if (StringUtils.isEmpty(jdbcUrl)) {
            throw new IllegalArgumentException("数据库连接URL不能为空");
        }
        if (StringUtils.isEmpty(schemaName)) {
            throw new IllegalArgumentException("Schema名称不能为空");
        }

        // 根据数据库类型构建适当的JDBC URL和查询SQL
        JdbcUtil.DatabaseType dbType = identifyDatabaseType(jdbcUrl);
        String tableQuerySql = getTableQuerySql(schemaName, dbType);

        // 执行查询并返回结果
        return executeTableQuery(tableQuerySql, jdbcUrl, entity.getUsername(), entity.getPassword(), schemaName, dbType);
    }

    /**
     * 获取表查询SQL
     */
    private static String getTableQuerySql(String schemaName, JdbcUtil.DatabaseType dbType) {
        switch (dbType) {
            case MYSQL:
                return "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?";
            case ORACLE:
                return "SELECT TABLE_NAME AS table_name, COMMENTS AS table_comment " +
                        "FROM ALL_TABLES t LEFT JOIN ALL_TAB_COMMENTS c ON " +
                        "t.TABLE_NAME = c.TABLE_NAME AND t.OWNER = c.OWNER " +
                        "WHERE t.OWNER = ?";
            case SQLSERVER:
                return "SELECT t.name AS table_name, ep.value AS table_comment " +
                        "FROM sys.tables t LEFT JOIN sys.extended_properties ep ON " +
                        "ep.major_id = t.object_id AND ep.minor_id = 0 AND " +
                        "ep.name = 'MS_Description' " +
                        "WHERE SCHEMA_NAME(t.schema_id) = ?";
            case POSTGRESQL:
                return "SELECT table_name, obj_description(pg_class.oid) AS table_comment " +
                        "FROM information_schema.tables " +
                        "JOIN pg_class ON pg_class.relname = table_name " +
                        "JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace " +
                        "WHERE table_schema = ?";
            case DB2:
                return "SELECT TABNAME AS table_name, REMARKS AS table_comment " +
                        "FROM SYSCAT.TABLES WHERE TABSCHEMA = ?";
            case SQLITE:
                return "SELECT name AS table_name, '' AS table_comment FROM sqlite_master WHERE type = 'table'";
            default:
                throw new UnsupportedOperationException("不支持的数据库类型: " + dbType);
        }
    }

    /**
     * 执行表查询
     */
    @SneakyThrows
    private static List<Map<String, Object>> executeTableQuery(String sql, String jdbcUrl,
                                                               String username, String password,
                                                               String schemaName, JdbcUtil.DatabaseType dbType) {
        try (Connection connection = ConnectionManager.getConnection(jdbcUrl, username, password);
             PreparedStatement statement = connection.prepareStatement(sql)) {

            // 设置参数
            if (!dbType.equals(JdbcUtil.DatabaseType.SQLITE)) {
                statement.setString(1, schemaName);
            }

            try (ResultSet resultSet = statement.executeQuery()) {
                List<Map<String, Object>> result = new ArrayList<>();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                while (resultSet.next()) {
                    Map<String, Object> row = new LinkedHashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnLabel(i);
                        Object value = resultSet.getObject(i);
                        row.put(columnName, value);
                    }
                    result.add(row);
                }

                return result;
            }
        }
    }
}
